package com.newegg.fritz;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class GenEntityMysql {
    //数据库连接
	private static final String URL ="jdbc:mysql://10.16.238.74:3306/datafeed";
	private static final String NAME = "root";
	private static final String PASS = "admin";
	private static final String DRIVER ="com.mysql.jdbc.Driver";
	
	
	public static void main(String[] args) throws Exception {
		String table = "Task";
		GenEntityMysql.mapper(table);
		GenEntityMysql.select(table);
		GenEntityMysql.insert(table);
		GenEntityMysql.update(table);
	}

	private static void mapper(String tablename) throws Exception{
		Class.forName(DRIVER);
    	Connection conn = null;
    	Statement statement = null;
    	ResultSet result = null;
    	try {
    		conn = DriverManager.getConnection(URL,NAME,PASS);
    		statement = conn.createStatement();
    		result = statement.executeQuery("show full columns from " + tablename);
    		while (result.next()) {
    			String field = result.getString("Field");
    			field = lineToHump(field);
    			String type = result.getString("Type");
    			type = getType(type);
    			String comment = result.getString("Comment");
    			if(comment != null && !"".equals(comment)){
    				System.out.println("/* " + comment + " */");
    			}
    			System.out.println("private " + type + " " + field + ";\n");
			}
		} finally{
			if(result != null){ result.close(); }
			if(statement != null){ statement.close(); }
			conn.close();
		}
	}
	
	private static void select(String tablename) throws Exception{
		Class.forName(DRIVER);
    	Connection conn = null;
    	Statement statement = null;
    	ResultSet result = null;
    	try {
    		conn = DriverManager.getConnection(URL,NAME,PASS);
    		statement = conn.createStatement();
    		result = statement.executeQuery("show full columns from " + tablename);
    		Map<String, String> fieldMaps = new LinkedHashMap<String, String>();
    		while (result.next()) {
    			String f1 = result.getString("Field");
    			String f2 = lineToHump(f1);
    			fieldMaps.put(f1, f2);
			}
    		System.out.println();
    		System.out.println();
    		System.out.println("SELECT " + String.join(",", fieldMaps.keySet()) + " FROM " + tablename);
    		System.out.println();
    		fieldMaps.forEach((f1, f2) ->{
    			if(!f1.equals(f2)){
    				System.out.println("@Result(property = \"" +f2+ "\",  column = \"" +f1+ "\")");
    			}
    		});
		} finally{
			if(result != null){ result.close(); }
			if(statement != null){ statement.close(); }
			conn.close();
		}
	}
	
	private static void insert(String tablename) throws Exception{
		Class.forName(DRIVER);
    	Connection conn = null;
    	Statement statement = null;
    	ResultSet result = null;
    	try {
    		conn = DriverManager.getConnection(URL,NAME,PASS);
    		statement = conn.createStatement();
    		result = statement.executeQuery("show full columns from " + tablename);
    		Map<String, String> fieldMaps = new LinkedHashMap<String, String>();
    		while (result.next()) {
    			String f1 = result.getString("Field");
    			String f2 = lineToHump(f1);
    			fieldMaps.put(f1, f2);
			}
    		StringBuilder builder = new StringBuilder();
    		builder.append("INSERT INTO ").append(tablename).append("(").append(String.join(",", fieldMaps.keySet()))
    			.append(") VALUES (");
    		fieldMaps.values().forEach(k->{
    			builder.append("#{").append(k).append("},");
    		});
    		builder.setLength(builder.length() - 1);
    		builder.append(");");
    		System.out.println();
    		System.out.println();
    		System.out.println(builder.toString());
		} finally{
			if(result != null){ result.close(); }
			if(statement != null){ statement.close(); }
			conn.close();
		}
	}
	
	private static void update(String tablename) throws Exception{
		Class.forName(DRIVER);
    	Connection conn = null;
    	Statement statement = null;
    	ResultSet result = null;
    	try {
    		conn = DriverManager.getConnection(URL,NAME,PASS);
    		statement = conn.createStatement();
    		result = statement.executeQuery("show full columns from " + tablename);
    		Map<String, String> fieldMaps = new LinkedHashMap<String, String>();
    		while (result.next()) {
    			String f1 = result.getString("Field");
    			String f2 = lineToHump(f1);
    			fieldMaps.put(f1, f2);
			}
    		StringBuilder builder = new StringBuilder("UPDATE ");
    		builder.append(tablename).append(" SET ");
    		fieldMaps.forEach((f1, f2)->{
    			builder.append(f1 + "=#{" + f2 + "},");
    		});
    		builder.setLength(builder.length() - 1);
    		builder.append(" WHERE id =#{id}");
    		System.out.println();
    		System.out.println();
    		System.out.println(builder.toString());
		} finally{
			if(result != null){ result.close(); }
			if(statement != null){ statement.close(); }
			conn.close();
		}
	}
	
	/**下划线转驼峰*/  
    public static String lineToHump(String str){  
        str = str.toLowerCase();
        Pattern linePattern = Pattern.compile("_(\\w)");
        Matcher matcher = linePattern.matcher(str);  
        StringBuffer sb = new StringBuffer();  
        while(matcher.find()){  
            matcher.appendReplacement(sb, matcher.group(1).toUpperCase());  
        }  
        matcher.appendTail(sb);  
        return sb.toString();  
    }

	private static String getType(String sqlType) {
		if(sqlType.indexOf("(")  != -1){
			sqlType = sqlType.substring(0, sqlType.indexOf("("));
		}
		if(sqlType.equalsIgnoreCase("bit") || sqlType.equalsIgnoreCase("blob")){
			return "Boolean";
		}else if(sqlType.equalsIgnoreCase("tinyint")){
			return "Byte";
		}else if(sqlType.equalsIgnoreCase("smallint")){
			return "Short";
		}else if(sqlType.equalsIgnoreCase("int")){
			return "Integer";
		}else if(sqlType.equalsIgnoreCase("bigint")){
			return "Long";
		}else if(sqlType.equalsIgnoreCase("float")){
			return "Float";
		}else if(sqlType.equalsIgnoreCase("decimal") || sqlType.equalsIgnoreCase("numeric") 
				|| sqlType.equalsIgnoreCase("real") || sqlType.equalsIgnoreCase("money") 
				|| sqlType.equalsIgnoreCase("smallmoney")){
			return "Double";
		}else if(sqlType.equalsIgnoreCase("varchar") || sqlType.equalsIgnoreCase("char") 
				|| sqlType.equalsIgnoreCase("nvarchar") || sqlType.equalsIgnoreCase("nchar") 
				|| sqlType.equalsIgnoreCase("text")){
			return "String";
		}else if(sqlType.equalsIgnoreCase("datetime") || sqlType.equalsIgnoreCase("timestamp")){
			return "Date";
		}else{
			throw new RuntimeException("not find type:" + sqlType);
		}
	}

}